﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for SqlDataAccessHelper
/// </summary>
namespace UTIL
{
    public static class SqlDataAccessHelper
    {
        private static SqlConnection connection = new SqlConnection(@"Data Source=VANVU-PC\SQLEXPRESS;Initial Catalog=ok;Integrated Security=True");
        private static bool connectStatus;
        private static string errorString;

        public static string ErrorString
        {
            get { return SqlDataAccessHelper.errorString; }
            set { SqlDataAccessHelper.errorString = value; }
        }

        public static void ConnectSQLServer()
        {
            if (!connectStatus)
            {
                try
                {
                    connection.Open();
                    connectStatus = true;
                }
                catch (Exception ex)
                {
                    errorString = ex.ToString();
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static SqlCommand CreateCommand(string procName)
        {
            SqlCommand cmd = new SqlCommand(procName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        public static void DisconnectSQLServer()
        {
            if (connectStatus) connection.Close();
        }

        public static DataSet LoadData(string procName)
        {
            if (connectStatus)
            {
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(procName, connection);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    errorString = ex.ToString();
                    return null;
                }
            }
            else return null;
        }

        public static DataSet LoadDataWithParameter(string procName, params SqlParameter[] Params)
        {
            if (connectStatus)
            {
                try
                {
                    SqlDataAdapter adt = new SqlDataAdapter(procName, connection);
                    SqlCommand cmd = adt.SelectCommand;
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (SqlParameter param in Params) cmd.Parameters.Add(param);

                    DataSet ds = new DataSet();
                    adt.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    errorString = ex.ToString();
                    return null;
                }
            }
            else return null;
        }

        public static bool ExecCmd(SqlCommand cmd)
        {
            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                return true;
            }
            catch (Exception ex)
            {
                errorString = ex.ToString();
                cmd.Connection.Close();
                return false;
            }
        }

    }
}